﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Xml.Linq;
using IBM.Data.DB2;
using System.Collections;

/// <summary>
/// DB2 数据库操作类
/// </summary>
public class DBdb2
{
    public DBdb2()
    { }

    static protected void CloseConn(DB2Connection db2con)
    {
        db2con.Close();
    }

    static protected DB2Connection GetConn()
    {
        string conn = System.Configuration.ConfigurationSettings.AppSettings["conDB2"].ToString();
        DB2Connection db2con = new DB2Connection(conn);

        return db2con;
    }

    /// <summary>
    /// 获取单一对象的值
    /// </summary>
    /// <param name="conStr"></param>
    /// <param name="sqlcmd"></param>
    /// <param name="errMsg"></param>
    /// <returns></returns>
    static public object RunSingle( string sqlcmd, out string errMsg)
    {
        errMsg = "";
        DB2Connection connect = GetConn();
        try
        {
            return DAO.DB2Helper.ExecuteScalar(connect, CommandType.Text, sqlcmd);
        }
        catch (Exception e)
        {
            errMsg = e.Message;
            return null;
        }
        finally
        {
            CloseConn(connect);
        }
    }

    /// <summary>
    /// 返回 DataSet
    /// </summary>
    /// <param name="sqlCmd">sql语句</param>
    /// <param name="errMsg"></param>
    /// <returns></returns>
    static public DataSet RunDataSet( string sqlCmd, out string errMsg)
    {
        errMsg = "";
        DB2Connection db = GetConn();

        try
        {
            return DAO.DB2Helper.ExecuteDataset(db, CommandType.Text, sqlCmd);

        }
        catch (Exception ce)
        {
            errMsg = ce.Message;
            return null;
        }
        finally
        {
            CloseConn(db);
        }
    }

    static public DataTable RunDataTable( string sqlCmd, out string errMsg)
    {
        DataSet ds = RunDataSet( sqlCmd, out errMsg);
        if (ds == null || ds.Tables == null || ds.Tables.Count == 0)
        {
            errMsg = string.Format("查询{0}返回数据集为空", sqlCmd);
            return null;
        }
        else
            return ds.Tables[0];
    }

    static public DataRow RunDataRow( string sqlCmd, out string errMsg)
    {
        DataTable table = RunDataTable( sqlCmd, out errMsg);
        if (table == null || table.Rows == null || table.Rows.Count == 0)
        {
            errMsg = string.Format("查询{0}返回数据集为空", sqlCmd);
            return null;
        }
        else
            return table.Rows[0];
    }

    static public int RunCommand( string sqlCmd, out string errMsg)
    {
        errMsg = "";
        DB2Connection connect = GetConn();
        try
        {
            return DAO.DB2Helper.ExecuteNonQuery(connect, CommandType.Text, sqlCmd);
        }
        catch (Exception e)
        {
            errMsg = e.Message;
            return 0;
        }
        finally
        {
            CloseConn(connect);
        }
    }

    #region  执行多条SQL语句，实现数据库事务。
    /// <summary>
    /// 执行多条SQL语句，实现数据库事务。
    /// </summary>
    /// <param name="SQLStringList">ArrayList</param>
    public static void ExecuteSqlTran(ArrayList sqlList, string ConString)
    {
        bool mustCloseConnection = false;
        using (DB2Connection conn = new DB2Connection(ConString))
        {
            conn.Open();
            using (DB2Transaction trans = conn.BeginTransaction())
            {
                DB2Command cmd = new DB2Command();
                try
                {
                    for (int i = 0; i < sqlList.Count; i++)
                    {
                        string cmdText = sqlList[i].ToString();
                        PrepareCommand(cmd, conn, trans, CommandType.Text, cmdText, null, out mustCloseConnection);
                        int val = cmd.ExecuteNonQuery();

                    }
                    trans.Commit();
                }
                catch
                {
                    trans.Rollback();
                    throw;
                }
                finally
                {
                    conn.Close();
                    cmd.Dispose();
                }
            }
        }
    }

    /// <summary>
    /// 预处理用户提供的命令,数据库连接/事务/命令类型/参数
    /// </summary>
    /// <param name="command">要处理的db2Command</param>
    /// <param name="connection">数据库连接</param>
    /// <param name="transaction">一个有效的事务或者是null值</param>
    /// <param name="commandType">命令类型 (存储过程,命令文本, 其它.)</param>
    /// <param name="commandText">存储过程名或都T-SQL命令文本</param>
    /// <param name="commandParameters">和命令相关联的db2Parameter参数数组,如果没有参数为'null'</param>
    /// <param name="mustCloseConnection"><c>true</c> 如果连接是打开的,则为true,其它情况下为false.</param>
    private static void PrepareCommand(DB2Command command, DB2Connection connection, DB2Transaction transaction, CommandType commandType, string commandText, DB2Parameter[] commandParameters, out bool mustCloseConnection)
    {
        if (command == null) throw new ArgumentNullException("command");
        if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText");

        // If the provided connection is not open, we will open it
        if (connection.State != ConnectionState.Open)
        {
            mustCloseConnection = true;
            connection.Open();
        }
        else
        {
            mustCloseConnection = false;
        }

        // 给命令分配一个数据库连接.
        command.Connection = connection;

        // 设置命令文本(存储过程名或SQL语句)
        command.CommandText = commandText;

        // 分配事务
        if (transaction != null)
        {
            if (transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
            command.Transaction = transaction;
        }

        // 设置命令类型.
        command.CommandType = commandType;

        // 分配命令参数
        if (commandParameters != null)
        {
            AttachParameters(command, commandParameters);
        }
        return;

    }
    /// <summary>
    /// 将db2Parameter参数数组(参数值)分配给db2Command命令.
    /// 这个方法将给任何一个参数分配DBNull.Value;
    /// 该操作将阻止默认值的使用.
    /// </summary>
    /// <param name="command">命令名</param>
    /// <param name="commandParameters">DB2Parameters数组</param>

    private static void AttachParameters(DB2Command command, DB2Parameter[] commandParameters)
    {
        if (command == null) throw new ArgumentNullException("command");
        if (commandParameters != null)
        {
            foreach (DB2Parameter p in commandParameters)
            {
                if (p != null)
                {
                    // 检查未分配值的输出参数,将其分配以DBNull.Value.
                    if ((p.Direction == ParameterDirection.InputOutput || p.Direction == ParameterDirection.Input) &&
                        (p.Value == null))
                    {
                        p.Value = DBNull.Value;
                    }
                    command.Parameters.Add(p);
                }
            }
        }
    }


    #endregion

    public static Hashtable ExecuteDataTablebySqlList(Hashtable ht, string conStr)
    {
        bool mustCloseConnection = false;

        Hashtable htNew = new Hashtable();
        DB2DataReader dr = null;

        using (DB2Connection conn = new DB2Connection(conStr))
        {
            conn.Open();
            using (DB2Transaction trans = conn.BeginTransaction())
            {
                DB2Command cmd = new DB2Command();
                try
                {
                    foreach (DictionaryEntry de in ht)
                    {
                        string cmdText = de.Value.ToString();
                        PrepareCommand(cmd, conn, trans, CommandType.Text, cmdText, null, out mustCloseConnection);
                        dr = cmd.ExecuteReader();

                        while (dr.Read())
                        {
                            htNew.Add(de.Key, dr[0].ToString());
                        }
                    }

                    //for (int i = 0; i < sqlList.Count; i++)
                    //{
                    //    string cmdText = sqlList[i].ToString();
                    //    PrepareCommand(cmd, conn, trans, CommandType.Text, cmdText, null, out mustCloseConnection);
                    //    dr = cmd.ExecuteReader();

                    //}
                    trans.Commit();
                }
                catch
                {
                    trans.Rollback();
                    throw;
                }
                finally
                {
                    conn.Close();
                    cmd.Dispose();
                }
            }
        }

        return htNew;
    }
}
